/**
 * 
 */
package userService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import constants.Constants;
import userEntity.CustomerEntity;
import userEntity.EmployeeEntity;

/**
 * @author Yokesh Kumar
 *
 */
public class CreateUserService {

	private Connection connection;
	private PreparedStatement statement;
	/**
	 * constructor to set the Connection
	 */
	public CreateUserService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
		} 
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}
	
	/**
	 * This method checks whether the loginId entered by the
	 * user is already present in the login table
	 * @param LoginId
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet checkLoginId(String LoginId) throws SQLException{
		ResultSet rs = null;
		String query = "select * from login where LoginId = ? ";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, LoginId);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	public ResultSet checkEmailId(String EmailId) throws SQLException{
		ResultSet rs = null;
		String query = "select * from user where emailId = ? ";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, EmailId);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method checks whether the phoneNumber entered by the
	 * user is already present in the user table
	 * @param phoneNumber
	 * @return
	 * @throws SQLException 
	 */
	public ResultSet checkPhonenumber(String phoneNumber) throws SQLException{
		ResultSet rs = null;
		String query = "select * from user where phoneNumber = ? ";
		try {
			statement = connection.prepareStatement(query);
			statement.setString(1, phoneNumber);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}
	
	/**
	 * This method checks whether the user is active or not
	 * @param phoneNumber
	 * @return
	 * @throws SQLException
	 */
	public ResultSet checkUserStatus(String phoneNumber) throws SQLException
	{
		ResultSet rs = null;
		String query = "Select * from login where userPhoneNumber=?";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1, phoneNumber);
			rs = statement.executeQuery();
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		finally{
			try {
				connection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
		
		return rs;
	}
	
	/**
	 * This method checks whether the user is active or not
	 * @param loginId
	 * @return
	 * @throws SQLException
	 */
	public ResultSet checkStatus(String loginId) throws SQLException
	{
		ResultSet rs = null;
		String query = "Select * from login where LoginId=?";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1, loginId);
			rs = statement.executeQuery();
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		finally{
			try {
				connection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
		return rs;
	}
	
	/**
	 * this method stores the user details in to the User table
	 * and customerTable
	 * @param user
	 * @return
	 * @throws SQLException 
	 */
	public void createCustomer(CustomerEntity customer) throws SQLException{
		String query = "INSERT INTO customer values(?,?,?)";
		String query1 = "INSERT INTO user values(?,?,?,?,?,?)";
		String query2 = "INSERT INTO login values(?,?,?,?,true)";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1, customer.getPhoneNumber());
			statement.setString(2,customer.getCustomerType());
			statement.setBoolean(3,customer.isMember());
			statement.executeUpdate();
			
			statement = connection.prepareStatement(query1);
			statement.setString(1,customer.getPhoneNumber());
			statement.setString(2,customer.getName());
			statement.setString(3,customer.getAddress());
			statement.setString(4,customer.getPincode());
			statement.setString(5,customer.getUserType());
			statement.setString(6,customer.getEmailID());
			System.out.println("User type" + customer.getUserType());
			
			statement.executeUpdate();

			statement = connection.prepareStatement(query2);
			statement.setString(1,customer.getLoginId());
			statement.setString(2,customer.getPwd());
			statement.setString(3,customer.getPhoneNumber());
			statement.setString(4,customer.getRole());
			statement.executeUpdate();
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		finally{
			try {
				connection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
	}
	
	/**
	 * This method stores the employee details to employee,
	 * user and login table while creating employee
	 * @param employee
	 * @throws SQLException 
	 */
	public void createEmployee(EmployeeEntity employee) throws SQLException{
		String query = "INSERT INTO employee values(?,null,?)";
		String query1 = "INSERT INTO user values(?,?,?,?,?,?)";
		String query2 = "INSERT INTO login values(?,?,?,?,1)";
		try {
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(query);
			statement.setString(1, employee.getPhoneNumber());
			statement.setString(2,employee.getDesignation());
			statement.executeUpdate();
			System.out.println("values entered into employee table");
			
			statement = connection.prepareStatement(query1);
			statement.setString(1,employee.getPhoneNumber());
			statement.setString(2,employee.getName());
			statement.setString(3,employee.getAddress());
			statement.setString(4,employee.getPincode());
			statement.setString(5,employee.getDesignation());
			statement.setString(6,employee.getEmailID());
			statement.executeUpdate();
			System.out.println("values entered in user table");

			statement = connection.prepareStatement(query2);
			statement.setString(1,employee.getLoginId());
			statement.setString(2,employee.getPwd());
			statement.setString(3,employee.getPhoneNumber());
			statement.setString(4,employee.getDesignation());
			statement.executeUpdate();
			System.out.println("Values entered in login table");
			connection.commit();
		} 
		catch (SQLException e1) {
			if(connection != null){
				System.out.println("transaction is rolled back");
				try {
					connection.rollback();
				} catch (SQLException e) {
					e.printStackTrace();
					throw e;
				}
			}
			e1.printStackTrace();
			throw e1;
		}
		finally{
			try {
				connection.setAutoCommit(true);
			}
			catch (SQLException e) {
				e.printStackTrace();
				throw e;
			}
		}
		}
	
	public void closeConnection(){
		try{
			if(statement != null){
				statement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}
}
